﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Text;

/// <summary>
/// ExcelOp 的摘要说明
/// </summary>
public class ExcelOp
{
	public ExcelOp()
	{
		//
		// TODO: 在此处添加构造函数逻辑
		//
	}


    #region 得到服务器上的Xls填充的DataTable
    /// <summary>
    /// 得到服务器上的Xls填充的DataTable
    /// </summary>
    /// <param name="path">xls在服务器上的绝对路径</param>
    /// <param name="fields">要读取的字段，以,分割，至少要有一个字段</param>
    /// <param name="cueInfo">获取错误信息</param>
    /// <returns>DataTable，为null读取失败</returns>
    public static DataTable getDataTableSell(string path, string fields, ref string cueInfo)
    {
        //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + _physicalPath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";
        string connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";
        //= "Provider=Microsoft.Jet.OLEDB.4.0;" + "Extended Properties=Excel 8.0;" + "data source=" + path;
        DataTable dtSell = null;
        try
        {
            DataSet dsSell = new DataSet();
            using (OleDbConnection odconn = new OleDbConnection(connStr))
            {
                using (OleDbDataAdapter odda = new OleDbDataAdapter(@"SELECT " + fields + " FROM [Sheet1$]", odconn))
                {
                    odda.Fill(dsSell, "sell");
                    if (dsSell != null && dsSell.Tables.Count > 0)
                    {
                        dtSell = dsSell.Tables[0];
                    }
                }
            }
        }
        catch
        {
            cueInfo += "从Excel读取数据失败!!\r";
        }
        return dtSell;
    }
    #endregion

    /// <summary>
    /// 读取DataSet中的数据到字符串中
    /// </summary>
    /// <param name="ds"></param>
    /// <returns></returns>
    public static string ExportTable(DataSet ds, string[] columnName)
    {
        StringBuilder sb = new StringBuilder();
        //data = ds.DataSetName + "\n"; 
        int count = 0;

        foreach (DataTable tb in ds.Tables)
        {
            //data += tb.TableName + "\n"; 
            sb.AppendLine("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=gb2312\">");
            sb.AppendLine("<table cellspacing=\"0\" cellpadding=\"5\" rules=\"all\" border=\"1\">");
            //写出列名 
            sb.AppendLine("<tr style=\"font-weight: bold; white-space: nowrap;\">");

            if (columnName == null)
            {
                foreach (DataColumn column in tb.Columns)
                {
                    sb.AppendLine("<td>" +Convert.ToString(SqlHelper.ToChgHTMLString( column.ColumnName)) + "</td>");
                }
            }
            else
            {
                foreach (string column in columnName)
                {
                    sb.AppendLine("<td>" +Convert.ToString(SqlHelper.ToChgHTMLString( column)+"&nbsp;" )+ "</td>");
                }
            }
            sb.AppendLine("</tr>");

            //写出数据 
            foreach (DataRow row in tb.Rows)
            {
                sb.Append("<tr>");
                foreach (DataColumn column in tb.Columns)
                {
                    // 如果是int的需要转换格式 
                    //if (column.ColumnName.Equals("mobile") || column.ColumnName.Equals("mobile"))
                    //    sb.Append("<td style=\"vnd.ms-excel.numberformat:@\">" + row[column].ToString() + "</td>");
                    //else
                    string strName=SqlHelper.ToChgHTMLString( Convert.ToString(row[column].ToString())) ;
                     
                    sb.Append("<td>" +strName+"&nbsp;" + "</td>");

                }
                sb.AppendLine("</tr>");
                count++;
            }
            sb.AppendLine("</table>");
        }

        return sb.ToString();
    }

    /// <summary>
    /// 导出DS到Excel文件中
    /// </summary>
    /// <param name="page"> 当前页的Page</param>
    /// <param name="fileName">文件的前缀名，如：Store，不需要加入.xls及唯一性，会自动生成。如果用DataSet默认的，直接赋值null</param>
    /// <param name="ds">目标数据集</param>
    public static void ExportDsToXls(Page page, string fileName, DataSet ds, string[] columnName)
    {

        page.Response.Clear();
        page.Response.Buffer = true;
        page.Response.Charset = "GB2312";
        //page.Response.Charset = "UTF-8";
        page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + System.DateTime.Now.ToString("_yyMMdd_hhmm") + ".xls");
        page.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
        page.Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 
        page.EnableViewState = false;
        page.Response.Write(ExportTable(ds, columnName));
        page.Response.End();

    }


}
